from openpyxl import load_workbook
from openpyxl import workbook
from openpyxl.styles import Alignment,Border,Side,Font,PatternFill,GradientFill
'''
wb = load_workbook('files/p1.xlsx')
sheet = wb.worksheets[0]

# 找到单元格，并修改单元格的内容
cell = sheet.cell(1,1)
cell.value = 'ID'

# 将excel文件保存到p2.xlsx文件中
wb.save('files/p2.xlsx')
'''

'''
# 新建excel文件写内容
from openpyxl import workbook

# 创建excel且会默认创建一个sheet（名称Sheet）
wb = workbook.Workbook()
sheet = wb.worksheets[0]  # sheet =[ wb['Sheet']
# 找到单元格，并修改单元格的内容
cell = sheet.cell(1,1)
cell.value = 'ID'

# 将excel文件保存到p2.xlsx文件中
wb.save('files/p2.xlsx')
'''

wb = workbook.Workbook()

'''
# 修改sheet名称
sheet = wb.worksheets[0]
sheet.title = '学生信息'
wb.save('files/p2.xlsx')
'''

'''
# 创建sheet并设置sheet颜色
sheet = wb.create_sheet('工作技术',0)
sheet.sheet_properties.tabColor = 'red'
wb.save('files/p2.xlsx')
'''

'''
# 默认打开的sheet
wb.active = 0
wb.save('files/p2.xlsx')
'''

'''
# 拷贝sheet
sheet = wb.create_sheet('工作计划')
sheet.sheet_properties.tabColor='1072BA'
new_sheet = wb.copy_worksheet(wb['Sheet'])
wb.save('files/p2.xlsx')
'''

'''
# 删除sheet
del wb['用户列表']
wb.save('files/p2.xlsx')
'''

sheet = wb.worksheets[0]

# 获取某个单元格修改值
'''
cell = sheet.cell(1,1)
cell.value = 'idd'
sheet['B3'] = '12'
wb.save('file/p1.xlsx')
'''

'''
# 获取某些单元格，修改值
cell_list = sheet["B2":'C3']
for row in cell_list:
    for col in row:
        col.value = 'newli'
wb.save('xxx')
'''

# 对齐方式
cell = sheet.cell(1,1)
# horizontal,水平方向对齐：general,left,center,right,fill,justify,centerContinus,distributed
# vertical,垂直方向对齐：top,center,bottom,justify,distributed
# text_rotation 旋转角度
# wrap_text 是否自动换行
# cell.alignment = Alignment(horizontal='center',vertical='distributed',text_rotation=45,wrap_text=True)

# 边框
'''
# side的style：dashDot,dashDotDot,dashed,dotted,double,hair,medium
# mediumDashDot,mediumDashDotDot,mediumDashed,slantDashDot,thick,thin
cell.border = Border(
    top=Side(style='thin',color='ffb6c1'),
    bottom=Side(style='dashed',color='ffb6c1'),
    left=Side(style='dashed',color='ffb6c1'),
    right=Side(style='thin',color='ffb6c1'),
    diagonal=Side(style='thin',color='ffb6c1'), #对角线
    diagonalUp=True,    # 左下~右上
    diagonalDown=True,  # 左上~右下
)   
wb.save('xxx')
'''

# 字体
# cell.font = Font(name='微软雅黑',size=45,color='red',underline='single')

# 背景色
# cell.fill = PatternFill('solid',fgColor='99ccff')

# 渐变背景色
# cell.fill = GradientFill('linear',stop=('ffffff','99ccff','000000'))

# 宽高    索引从1开始
# sheet.row_dimensions[1].height = 50
# sheet.column_dimensions['D'].width = 100

# 合并单元格
# sheet.merge_cells('A1:A3')
# sheet.merge_cells(start_row=2,start_column=1,end_row=4,end_column=3)

# sheet.unmerge_cells("A1:A3")

# 写入公式
# sheet['C1'] = '合计'
# sheet['C2'] = 'B2*C2'
# sheet['C3'] = SUM(c1,b3)

# 删除
# idx,要删除索引位置
# amount,从索引位置开始要删除的个数(默认为1)
# sheet.delete_rows(idx=1,amount=2)
# sheet.delete_cols(idx=1,amount=3)

# 插入
# sheet.insert_rows(idx=5,amount=10)
# sheet.insert_cols(idx=5,amount=10)


# 循环写内容
# cell_range = sheet["A1:C3"]
# for row in cell_range:
#     for cell in row:
#         cell.value = 'xx'
#
# for row in sheet.iter_rows(min_row=2,min_col=1,max_row=4,max_col=4):
#     for cell in row:
#         cell.value = 'oo'

# 移动
# 将H2:J10范围的数据，向右移动15个位置，向上移动1个位置
# sheet.move_range('H2:J10',rows=-1,cols=15)


# sheet['D1'] = '合计'
# sheet['D2'] = 'B2*C2'
# sheet['D3'] = SUM(B2*C2)
# sheet.move_range('B1:C3',cols=10,translate=True) # 自动处理公式

# 打印区域
# sheet.print_area = 'A1:D200'

# 打印时，每个页面的固定表头，
sheet.print_title_cols = 'A:D'
sheet.print_title_rows = '1:3'
